package com.datasource.repository.impl;

import com.datasource.entity.*;
import com.datasource.repository.DataRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

@Repository
public class DataRepositoryImpl implements DataRepository {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Override
  public void insertFilm(List<Film> films) {
    if (CollectionUtils.isEmpty(films)) {
      return;
    }


    insertBatch("film", films.stream().map(Film::getName).collect(Collectors.toList()));
  }

  @Override
  public void insertUser(List<User> users) {
    if (CollectionUtils.isEmpty(users)) {
      return;
    }

    List<String> sql = getInsertUserSql(users);
    sql.forEach(jdbcTemplate::execute);
  }

  @Override
  public void insertInvoice(List<Invoice> invoices) {
    if (CollectionUtils.isEmpty(invoices)) {
      return;
    }

    List<String> sql = getInsertInvoiceSql(invoices);
    sql.forEach(jdbcTemplate::execute);
  }

  @Override
  public List<User> getUserByAgeOver(int age) {
    String sql = "select SQL_NO_CACHE id from user where age>" + age + ";";
    return jdbcTemplate.query(sql, new RowMapper<User>() {
      @Override
      public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user = new User();
        user.setId(resultSet.getLong("id"));
        return user;
      }
    });
  }

  @Override
  public List<Invoice> getInvoiceByAmountOver(int amount) {
    String sql = "select SQL_NO_CACHE `name`, amount, user_id from invoice where amount>" + amount + " ORDER BY amount;";
    return jdbcTemplate.query(sql, new RowMapper<Invoice>() {
      @Override
      public Invoice mapRow(ResultSet resultSet, int i) throws SQLException {
        Invoice invoice = new Invoice();
        invoice.setName(resultSet.getString("name"));
        invoice.setAmount(resultSet.getInt("amount"));
        invoice.setUserId(resultSet.getLong("user_id"));
        return invoice;
      }
    });
  }

  @Override
  public List<Invoice> getInvoiceByAgeAndAmount(int age, int amount) {
    String sql = "SELECT SQL_NO_CACHE `name`, amount FROM invoice WHERE age>" + age + " AND amount>" + amount + " ORDER BY amount;";
    return jdbcTemplate.query(sql, new RowMapper<Invoice>() {
      @Override
      public Invoice mapRow(ResultSet resultSet, int i) throws SQLException {
        Invoice invoice = new Invoice();
        invoice.setName(resultSet.getString("name"));
        invoice.setAmount(resultSet.getInt("amount"));
        return invoice;
      }
    });
  }

  @Override
  public void insertMessage(List<Message> messages) {
    String sql = getInsertMessageSql(messages);
    jdbcTemplate.execute(sql);
  }

  @Override
  public void synchronizeEmailInActor() {
    for (int i = 0; i < 10000000; i++) {
      String email = jdbcTemplate.queryForObject("select email from actor_tmp WHERE id=" + (i + 1), new RowMapper<String>() {
        @Override
        public String mapRow(ResultSet resultSet, int i) throws SQLException {
          return resultSet.getString("email");
        }
      });

      jdbcTemplate.execute("update actor4 set email1='" + email + "' where id=" + (i + 1));
      System.out.println("success count: " + (i + 1));
    }
  }

  @Override
  public void updateEmailInActor(List<String> emails, int index) {
    jdbcTemplate.execute(getUpdateEmailSql(emails, index));
  }

  private String getUpdateEmailSql(List<String> emails, int index) {
    StringBuilder result = new StringBuilder("insert into actor_tmp (id, email) values ");
    result.append("(").append(++index).append(", '").append(emails.get(0)).append("')");
    for (int i = 1; i < emails.size(); i++) {
      result.append(",(").append(++index).append(", '").append(emails.get(i)).append("')");
    }
    return result.toString();
  }

  private String getInsertMessageSql(List<Message> messages) {
    StringBuilder builder = new StringBuilder("insert into message (id, content, actor_id) values ");
    builder.append("(" + messages.get(0).getId() + ", '" + messages.get(0).getContent() + "', " + messages.get(0).getActorId() + ")");
    for (int i = 1; i < messages.size(); i++) {
      builder.append(",(" + messages.get(i).getId() + ", '" + messages.get(i).getContent() + "', " + messages.get(i).getActorId() + ")");
    }
    builder.append(";");
    return builder.toString();
  }

  @Override
  public void createRandomDictionary(List<Dictionary> dictionaries) {
    if (CollectionUtils.isEmpty(dictionaries)) {
      return;
    }

    insertBatch("dictionary", dictionaries.stream().map(Dictionary::getName).collect(Collectors.toList()));
  }

  @Override
  public List<Dictionary> getAllDictionaries() {
    String sql = "select * from dictionary;";
    return jdbcTemplate.query(sql, new RowMapper<Dictionary>() {
      @Override
      public Dictionary mapRow(ResultSet resultSet, int i) throws SQLException {
        Dictionary dictionary = new Dictionary();
        dictionary.setId(resultSet.getLong("id"));
        dictionary.setName(resultSet.getString("name"));
        return dictionary;
      }
    });
  }

  @Override
  public void insertActor(List<Actor> actors) {
    if (CollectionUtils.isEmpty(actors)) {
      return;
    }

    List<String> sql = getInsertActorSql(actors);
    sql.forEach(jdbcTemplate::execute);
  }

  @Override
  public void insertFilmWithId(List<Film> films) {
    if (CollectionUtils.isEmpty(films)) {
      return;
    }

    List<String> sql = getInsertSqlWithId(films);
    sql.forEach(jdbcTemplate::execute);
  }

  private List<String> getInsertSqlWithId(List<Film> films) {
    List<String> results = new ArrayList<>();

    int from = 0, size = 50000;
    List<Film> subFilms = subList(films, from, size);
    while (!subFilms.isEmpty()) {
      StringBuilder sql = new StringBuilder("insert into film_tmp (id, name) values ");
      sql.append("(").append(subFilms.get(0).getId()).append(", '").append(subFilms.get(0)).append("')");
      for (int i = 1; i < subFilms.size(); i++) {
        sql.append(", (").append(subFilms.get(i).getId()).append(", '").append(subFilms.get(i).getName()).append("')");
      }

      results.add(sql.append(";").toString());
      from = from + size;
      subFilms = subList(films, from, size);
    }

    return results;
  }

  private List<String> getInsertActorSql(List<Actor> actors) {
    List<String> results = new ArrayList<>();
    int from = 0, size = 10000;
    List<Actor> subActors = subList(actors, from, size);
    while (!subActors.isEmpty()) {
      StringBuilder sql = new StringBuilder("insert into actor (first_name, last_name, email, gender) values ");
      sql.append("(").append(getActorValues(subActors.get(0))).append(")");
      for (int i = 1; i < subActors.size(); i++) {
        sql.append(", (").append(getActorValues(subActors.get(i))).append(")");
      }

      results.add(sql.append(";").toString());
      from = from + size;
      subActors = subList(actors, from, size);
    }

    return results;
  }

  private List<String> getInsertUserSql(List<User> users) {
    List<String> results = new ArrayList<>();
    int from = 0, size = 50000;
    List<User> subUsers = subList(users, from, size);
    while (!subUsers.isEmpty()) {
      StringBuilder sql = new StringBuilder("insert into user (name, age) values ");
      sql.append("('").append(subUsers.get(0).getName()).append("', ").append(subUsers.get(0).getAge()).append(")");
      for (int i = 1; i < subUsers.size(); i++) {
        sql.append(", ('").append(subUsers.get(i).getName()).append("', ").append(subUsers.get(i).getAge()).append(")");
      }

      results.add(sql.append(";").toString());
      from = from + size;
      subUsers = subList(users, from, size);
    }

    return results;
  }

  private List<String> getInsertInvoiceSql(List<Invoice> invoices) {
    List<String> results = new ArrayList<>();
    int from = 0, size = 10000;
    List<Invoice> subInvoice = subList(invoices, from, size);
    while (!subInvoice.isEmpty()) {
      StringBuilder sql = new StringBuilder("insert into invoice (name, amount, user_id) values ");
      Invoice firstInvoice = subInvoice.get(0);
      sql.append("('").append(firstInvoice.getName()).append("', ").append(firstInvoice.getAmount()).append(",").append(firstInvoice.getUserId()).append(")");
      for (int i = 1; i < subInvoice.size(); i++) {
        Invoice invoice = subInvoice.get(i);
        sql.append(", ('").append(invoice.getName()).append("', ").append(invoice.getAmount()).append(",").append(invoice.getUserId()).append(")");
      }

      results.add(sql.append(";").toString());
      from = from + size;
      subInvoice = subList(invoices, from, size);
    }

    return results;
  }

  private String getActorValues(Actor actor) {
    return "'" + actor.getFirstName() + "', '" + actor.getLastName() + "', '" + actor.getEmail() + "', " + actor.getGender();
  }

  private void insertBatch(String table, List<String> names) {
    int from = 0, size = 10000;
    List<String> subNames = subList(names, from, size);
    while (!subNames.isEmpty()) {
      List<String> results = new ArrayList<>();
      StringBuilder sql = new StringBuilder("insert into " + table + " (name) values ");
      sql.append("('").append(subNames.get(0)).append("')");
      for (int i = 1; i < subNames.size(); i++) {
        sql.append(", ('").append(subNames.get(i)).append("')");
      }

      results.add(sql.append(";").toString());
      from = from + size;
      subNames = subList(names, from, size);

      results.forEach(jdbcTemplate::execute);
    }
  }

  private <T> List<T> subList(List<T> items, int from, int size) {
    if (items.size() <= from) {
      return Collections.emptyList();
    }

    if (items.size() >= from + size) {
      return items.subList(from, from + size);
    }

    return items.subList(from, items.size());
  }
}
